# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html

import pymysql
from twisted.enterprise import adbapi

class AnjukeScrapyPipeline(object):
    # def __init__(self, dbpool):
    #     self.dbpool = dbpool
    # def process_item(self, item, spider):
    #     """
    #         使用twisted将MySQL插入变成异步执行。通过连接池执行具体的sql操作，返回一个对象
    #     """
    #     query = self.dbpool.runInteraction(self.do_insert, item)  # 指定操作方法和操作数据
    #     # 添加异常处理
    #     query.addCallback(self.handle_error)  # 处理异常
    #
    # def do_insert(self, cursor, item):
    #     # 对数据库进行插入操作，并不需要commit，twisted会自动commit
    #     # title = scrapy.Field()
    #     # house = scrapy.Field()
    #     # house_big = scrapy.Field()
    #     # house_build_year = scrapy.Field()
    #     # house_location = scrapy.Field()
    #     # total_price = scrapy.Field()
    #     # unit_price = scrapy.Field()
    #     # city = scrapy.Field()
    #     # area = scrapy.Field()
    #     insert_sql = """
    #             insert into anjuke(title,house,house_big,house_build_year,house_location,total_price,unit_price,city,area) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)
    #                  """
    #     #pymysql.escape_string
    #     cursor.execute(insert_sql, (item['title'], item['house'], item['house_big'], item['house_build_year'],
    #                                     item['house_location'],item['total_price'],item['unit_price'],item['city'],item['area']))
    #
    # def handle_error(self, failure):
    #     if failure:
    #         # 打印错误信息
    #         print(failure)
    # @classmethod
    # def from_settings(cls, settings):  # 函数名固定，会被scrapy调用，直接可用settings的值
    #     """
    #     数据库建立连接
    #     :param settings: 配置参数
    #     :return: 实例化参数
    #     """
    #     adbparams = dict(
    #         host=settings['MYSQL_HOST'],
    #         db=settings['MYSQL_DBNAME'],
    #         user=settings['MYSQL_USER'],
    #         password=settings['MYSQL_PASSWORD'],
    #         cursorclass=pymysql.cursors.DictCursor  # 指定cursor类型
    #     )
    #     # 连接数据池ConnectionPool，使用pymysql或者Mysqldb连接
    #     dbpool = adbapi.ConnectionPool('pymysql', **adbparams)
    #     # 返回实例化参数
    #     return cls(dbpool)

    """
        同步操作
    """

    def __init__(self):
        # 建立连接
        self.conn = pymysql.connect('localhost', 'root', '286348794zz', "crawling_learning")  # 有中文要存入数据库的话要加charset='utf8'
        # 创建游标
        self.cursor = self.conn.cursor()

    def process_item(self, item, spider):
        # sql语句
        insert_sql = """
                      insert into anjuke(title,house,house_big,house_build_year,house_location,total_price,unit_price,city,area) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)
                           """
        # pymysql.escape_string
        self.cursor.execute(insert_sql, (item['title'], item['house'], item['house_big'], item['house_build_year'],
                                    item['house_location'], item['total_price'], item['unit_price'], item['city'],
                                    item['area']))
        # 提交，不进行提交无法保存到数据库
        self.conn.commit()

    def close_spider(self, spider):
        # 关闭游标和连接
        self.cursor.close()
        self.conn.close()







